﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;

namespace AlexandriaPressPublisherDataAccessLayer
{
    public class ProofreaderDataAccessManager
    {
        /// <summary>
        /// Sets up ConnectionString
        /// </summary>
        private static string myConnectionString
        {
            get { return ConfigurationManager.ConnectionStrings["BookConn1"].ConnectionString; }
        }
        
        public static DateTime getDueDate(int id, ref int draftID)
        {
            #region "Variable Declaration"
            DateTime dateAssigned = DateTime.MinValue;
            #endregion

            #region "SqlConnection Declaration"
            SqlConnection connection = new SqlConnection();
            connection.ConnectionString = myConnectionString;
            #endregion

            #region "Open Connection"
            if (connection.State != ConnectionState.Open)
            {
                connection.Open();
            }
            #endregion

            #region "SqlCommand Declaration"
            SqlCommand command = new SqlCommand();
            command.Connection = connection;
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = "usp_Get_Deadline";
            command.Connection = connection;
            #endregion
      
            #region "Parameter Declaration"
            SqlParameter parameter = new SqlParameter();
            parameter.DbType = DbType.Int32;
            parameter.Value = id;
            parameter.ParameterName = "@UserID";
            parameter.Direction = ParameterDirection.Input;
            command.Parameters.Add(parameter);
            #endregion

            #region "Execute Command"
            SqlDataReader dataReader = command.ExecuteReader();
            #endregion

            #region "Transfer Data to DateTime Variable"
            while (dataReader.Read())
            {
                dateAssigned = dataReader.GetDateTime(dataReader.GetOrdinal("DateAssignedToProofReader"));
                draftID = dataReader.GetInt32(dataReader.GetOrdinal("DraftID"));
            }
            #endregion

            #region "Closing Connection"            
            connection.Close();
            #endregion

            return dateAssigned;
        }

        public static int saveReviewedDraft(string path, int userID, int draftID)
        {
            #region "Variable Declaration"
            int i = 0;
            #endregion

            #region "SqlConnection Declaration"
            SqlConnection connection = new SqlConnection();
            connection.ConnectionString = myConnectionString;
            #endregion

            #region "Open Connection"
            if (connection.State != ConnectionState.Open)
            {
                connection.Open();
            }
            #endregion

            #region "SqlTransaction Declaration"
            SqlTransaction transaction = connection.BeginTransaction();
            #endregion

            #region "SqlCommand Declaration"
            SqlCommand command1 = new SqlCommand();
            command1.Connection = connection;
            command1.CommandType = CommandType.StoredProcedure;
            command1.CommandText = "usp_Update_Assignment";
            command1.Connection = connection;

            SqlCommand command2 = new SqlCommand();
            command2.Connection = connection;
            command2.CommandType = CommandType.StoredProcedure;
            command2.CommandText = "usp_Update_Draft";
            command2.Connection = connection;
            #endregion

            #region "Parameter Declaration"
            SqlParameter parameter1 = new SqlParameter();
            parameter1.DbType = DbType.Int32;
            parameter1.Value = userID;
            parameter1.ParameterName = "@ProofReaderId";
            parameter1.Direction = ParameterDirection.Input;
            command1.Parameters.Add(parameter1);

            SqlParameter parameter2 = new SqlParameter();
            parameter2.DbType = DbType.Int32;
            parameter2.Value = draftID;
            parameter2.ParameterName = "@DraftId";
            parameter2.Direction = ParameterDirection.Input;
            command1.Parameters.Add(parameter2);

            SqlParameter parameter3 = new SqlParameter();
            parameter3.DbType = DbType.Int32;
            parameter3.Value = draftID;
            parameter3.ParameterName = "@DraftId";
            parameter3.Direction = ParameterDirection.Input;
            command2.Parameters.Add(parameter3);

            SqlParameter parameter4 = new SqlParameter();
            parameter4.DbType = DbType.AnsiString;
            parameter4.Value = path;
            parameter4.ParameterName = "@FileLoc";
            parameter4.Direction = ParameterDirection.Input;
            command2.Parameters.Add(parameter4);
            #endregion

            #region "Execute Command"
            command1.Transaction = transaction;
            command2.Transaction = transaction;
            
            try
            {
                i = command2.ExecuteNonQuery();
                i = command1.ExecuteNonQuery();
                transaction.Commit();
            }
            catch (Exception err)
            {
                transaction.Rollback();
            }
            #endregion

            #region "Closing Connection"
            transaction.Dispose();
            connection.Close();
            #endregion  
      
            return i;
        }

        public static DataTable selectAllUnassignedDrafts()
        {
            #region "Variable Declaration"
            DataSet allDrafts = new DataSet();
            #endregion

            #region "Data Adapter"
            SqlDataAdapter sqlAdapter = new SqlDataAdapter();
            #endregion

            #region "Sql Connection and SQL Command"
            SqlCommand comm = new SqlCommand("usp_Find_All", new SqlConnection(myConnectionString));
            comm.CommandType = CommandType.StoredProcedure;

            sqlAdapter.SelectCommand = comm;
            #endregion

            #region "Fill data set"
            try
            {
                sqlAdapter.Fill(allDrafts);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                comm.Dispose();
                sqlAdapter.Dispose();
                comm = null;
                sqlAdapter = null;
            }
            #endregion

            return allDrafts.Tables[0];
            
        }

        public static string assignProofreader(int slectedDraftID, int proofreaderID, ref int numHits)
        {
            #region "Variable Declaration"
            string filename = null;    
            #endregion

            #region "SqlConnection Declaration"
            SqlConnection connection = new SqlConnection();
            connection.ConnectionString = myConnectionString;
            #endregion

            #region "Open Connection"
            if (connection.State != ConnectionState.Open)
            {
                connection.Open();
            }
            #endregion

            #region "SqlCommand Declaration"
            SqlCommand command1 = new SqlCommand();
            command1.Connection = connection;
            command1.CommandType = CommandType.StoredProcedure;
            command1.CommandText = "usp_Add_Assignment";
            command1.Connection = connection;

            SqlCommand command2 = new SqlCommand();
            command2.Connection = connection;
            command2.CommandType = CommandType.StoredProcedure;
            command2.CommandText = "usp_Select_Draft_Title";
            command2.Connection = connection;

            SqlCommand command3 = new SqlCommand();
            command3.Connection = connection;
            command3.CommandType = CommandType.StoredProcedure;
            command3.CommandText = "usp_Check_Redundancy";
            command3.Connection = connection;
            #endregion 
       
            #region "Parameter Declaration"
            SqlParameter parameter1 = new SqlParameter();
            parameter1.DbType = DbType.Int32;
            parameter1.Value = proofreaderID;
            parameter1.ParameterName = "@ProofReaderId";
            parameter1.Direction = ParameterDirection.Input;
            command1.Parameters.Add(parameter1);

            SqlParameter parameter2 = new SqlParameter();
            parameter2.DbType = DbType.Int32;
            parameter2.Value = slectedDraftID;
            parameter2.ParameterName = "@DraftId";
            parameter2.Direction = ParameterDirection.Input;
            command1.Parameters.Add(parameter2);

            SqlParameter parameter3 = new SqlParameter();
            parameter3.DbType = DbType.Int32;
            parameter3.Value = slectedDraftID;
            parameter3.ParameterName = "@DraftId";
            parameter3.Direction = ParameterDirection.Input;
            command2.Parameters.Add(parameter3);

            SqlParameter parameter4 = new SqlParameter();
            parameter4.DbType = DbType.Int32;
            parameter4.Value = slectedDraftID;
            parameter4.ParameterName = "@id";
            parameter4.Direction = ParameterDirection.Input;
            command3.Parameters.Add(parameter4);
            #endregion

            #region "SqlTransaction Declaration"
            SqlTransaction transaction = connection.BeginTransaction();
            #endregion

            #region "Execute Command"
            command1.Transaction = transaction;
            command2.Transaction = transaction;
            command3.Transaction = transaction;

            try
            {
                SqlDataReader dataCount = command3.ExecuteReader();
                while (dataCount.Read())
                {
                    numHits = dataCount.GetInt32(dataCount.GetOrdinal("HitCount"));
                }
                dataCount.Dispose();

                if (numHits == 0)
                {
                    command1.ExecuteNonQuery();
                    SqlDataReader dataReader = command2.ExecuteReader();

                    while (dataReader.Read())
                    {
                        filename = dataReader.GetString(dataReader.GetOrdinal("FileLocation"));
                    }
                    dataReader.Dispose();
                    transaction.Commit();
                }
            }
            catch (Exception err)
            {
                transaction.Rollback();
            }
            #endregion

            #region "Closing Connection"
            transaction.Dispose();
            connection.Close();
            #endregion

            return filename;
        }

        public static int checkUnassignedUser(int userID)
        {
            #region "Variable Declaration"
            int numDraftsAssignedToUser = 0;
            #endregion

            #region "SqlConnection Declaration"
            SqlConnection connection = new SqlConnection();
            connection.ConnectionString = myConnectionString;
            #endregion

            #region "Open Connection"
            if (connection.State != ConnectionState.Open)
            {
                connection.Open();
            }
            #endregion

            #region "SqlCommand Declaration"
            SqlCommand command = new SqlCommand();
            command.Connection = connection;
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = "usp_Check_Unassigned_User";
            command.Connection = connection;
            #endregion

            #region "Parameter Declaration"
            SqlParameter parameter1 = new SqlParameter();
            parameter1.DbType = DbType.Int32;
            parameter1.Value = userID;
            parameter1.ParameterName = "@userID";
            parameter1.Direction = ParameterDirection.Input;
            command.Parameters.Add(parameter1);

            SqlParameter parameter2 = new SqlParameter();
            parameter2.DbType = DbType.Int32;
            parameter2.ParameterName = "@numHits";
            parameter2.Direction = ParameterDirection.Output;
            command.Parameters.Add(parameter2);
            #endregion

            #region "Execute Command"
            SqlDataReader dataReader = command.ExecuteReader();
            #endregion

            #region "Transfer Data to DateTime Variable"
            numDraftsAssignedToUser = Convert.ToInt32(parameter2.Value);
            #endregion

            #region "Closing Connection"
            connection.Close();
            #endregion

            return numDraftsAssignedToUser;            
        }

        public static int uploadDraft(int userID, int draftID, string prooferRecommendation, string prooferComment)
        {
            if (prooferRecommendation == "Deny")
            {
                prooferRecommendation = "Draft_Denied";
            }
            if (prooferRecommendation == "Send Back for Editing")
            {
                prooferRecommendation = "Draft_For_Editing";
            }

            #region "Variable Declaration"
            int numEntries = 0;
            #endregion

            #region "SqlConnection Declaration"
            SqlConnection connection = new SqlConnection();
            connection.ConnectionString = myConnectionString;
            #endregion

            #region "Open Connection"
            if (connection.State != ConnectionState.Open)
            {
                connection.Open();
            }
            #endregion

            #region "SqlTransaction Declaration"
            SqlTransaction transaction = connection.BeginTransaction();
            #endregion

            #region "SqlCommand Declaration"
            SqlCommand command = new SqlCommand();
            command.Connection = connection;
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = "usp_Update_Findings";
            command.Connection = connection;

            SqlCommand command1 = new SqlCommand();
            command1.Connection = connection;
            command1.CommandType = CommandType.StoredProcedure;
            command1.CommandText = "usp_Update_Assignment";
            command1.Connection = connection;
            #endregion   
         
            #region "Parameter Declaration"
            SqlParameter parameter1 = new SqlParameter();
            parameter1.DbType = DbType.Int32;
            parameter1.Value = draftID;
            parameter1.ParameterName = "@draftID";
            parameter1.Direction = ParameterDirection.Input;
            command.Parameters.Add(parameter1);

            SqlParameter parameter2 = new SqlParameter();
            parameter2.DbType = DbType.String;
            parameter2.Value = prooferRecommendation;
            parameter2.ParameterName = "@recommendation";
            parameter2.Direction = ParameterDirection.Input;
            command.Parameters.Add(parameter2);

            SqlParameter parameter3 = new SqlParameter();
            parameter3.DbType = DbType.String;
            parameter3.Value = prooferComment.Trim().Replace("'", "''");
            parameter3.ParameterName = "@comment";
            parameter3.Direction = ParameterDirection.Input;
            command.Parameters.Add(parameter3);

            SqlParameter parameter4 = new SqlParameter();
            parameter4.DbType = DbType.Int32;
            parameter4.Value = userID;
            parameter4.ParameterName = "@ProofReaderId";
            parameter4.Direction = ParameterDirection.Input;
            command1.Parameters.Add(parameter4);

            SqlParameter parameter5 = new SqlParameter();
            parameter5.DbType = DbType.Int32;
            parameter5.Value = draftID;
            parameter5.ParameterName = "@DraftId";
            parameter5.Direction = ParameterDirection.Input;
            command1.Parameters.Add(parameter5);
            #endregion

            #region "Execute Command"
            command.Transaction = transaction;
            command1.Transaction = transaction;

            try
            {
                numEntries = command.ExecuteNonQuery();
                numEntries += command1.ExecuteNonQuery();
                transaction.Commit();
            }
            catch
            {
                transaction.Rollback();
            }
            #endregion

            #region "Closing Connection"
            transaction.Dispose();
            connection.Close();
            #endregion

            return numEntries;
        }
    }
}
